<?php

namespace App\Http\Controllers\Gas;

use App\Http\Controllers\Controller;
use App\Http\Controllers\HelperController;
use App\Imports\Gas\GasImport;
use App\Models\Gas\GasSk;
use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Facades\Excel;

class GasController extends Controller
{
    public function gasList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getGasList($params)->get()->toArray();
        $dataCount = $this->getGasList($countParams)->count();

        return response()->formatJson(['gasRecord' => $data, 'count' => $dataCount]);
    }

    public function getGasList($params): Builder
    {
        $query = GasSk::query();

        if (isset($params['license_plate_num'])) {
            $query->where('license_plate_num', 'like', '%' . $params['license_plate_num'] . '%');
        }

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('line', $params['line']);
        }

        if ($params['date_time'][0] !== null && $params['date_time'][1] !== null) {
            $startDate = date('Y-m-d 00:00:00', strtotime($params['date_time'][0]));
            $endDate = date('Y-m-d 23:59:59', strtotime($params['date_time'][1]));
            $query->whereBetween('date_time', [$startDate, $endDate]);
        }

        if (isset($params['pageNumber'], $params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query->orderByDesc('date_time');
    }

    /**
     * 导入加气记录
     *
     * @param  Request  $request
     *
     * @return mixed
     * @throws Exception
     */
    public function importGas(Request $request)
    {
        $urlParse = parse_url($request->input('url'));
        $excelData = Excel::toArray(new GasImport, public_path() . $urlParse['path']);
        $fullData = $excelData[0];

        // 删除前三行
        array_shift($fullData);
        array_shift($fullData);
        array_shift($fullData);
        array_pop($fullData);

        $data = [];
        $fields = [
            'date_time',
            'gun_num',
            'serial_num',
            'post_num',
            'gas_count',
            'gas_amt',
            'gas_unit_amt',
            'card_region_num',
            'card_operator_num',
            'user_card_num',
            'card_type',
            'car_type',
            'license_plate_num',
            'line',
            'consumption_patterns',
            'card_balance',
            'team',
            'employee_card_num',
            'is_paid',
            'cashier',
            'sign',
            'start_pressure',
            'end_pressure',
            'add_gas_long',
            'stop_all_gas',
            'offset',
            'density',
        ];
        // 整理数据，填充合并的单元格数据
        foreach ($fullData as $row) {
            $item = [];
            foreach ($fields as $index => $field) {
                $item[$field] = $row[$index];
            }
            if ($item['license_plate_num']) {
                $item['license_plate_num'] = Str::of($item['license_plate_num'])->replace('陕G', '');
            }
            $item['date_time'] = HelperController::transformDateTime($item['date_time'], 'Y-m-d H:i:s');
            $item['created_at'] = date('Y-m-d H:i:s');

            // 检测数据是否重复
            $count = GasSk::query()
                ->where('date_time', $item['date_time'])
                ->where('license_plate_num', $item['license_plate_num'])
                ->count();
            if ($count) {
                GasSk::query()
                    ->where('date_time', $item['date_time'])
                    ->where('license_plate_num', $item['license_plate_num'])
                    ->delete();
            }

            $data[] = $item;
        }
        $result = GasSk::query()->insert($data);
        return response()->formatJson($result, '导入成功');
    }

    public function deleteDuplicateData()
    {
        $deleteCount = DB::delete(
            'DELETE
            FROM
                akbus_gas_sk
            WHERE
                id IN (
                SELECT
                    a.id
                FROM
                    (
                    SELECT
                        id,
                        count( id ) AS num
                    FROM
                        akbus_gas_sk ags
                    GROUP BY
                        ags.license_plate_num,
                        ags.date_time,
                        ags.gas_amt
                    HAVING
                    num > 1
                ) a)'
        );

        return response()->formatJson(['deleteCount' => $deleteCount]);
    }
}
